﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BaseData;
using BaseData.Common;
using TaxEntity.User;

namespace TaxData.User
{
    /// <summary>
    /// 用户数据层
    /// </summary>
    public class UserData
    {
        public UserData()
        {
        }

        /// <summary>
        /// 判断用户是否存在
        /// </summary>
        public bool Exists(string userName)
        {
            string sql = "select count(1) from AdmUser with(nolock) where userName = @userName and status=1";
            SqlParameter p = new SqlParameter("@userName", SqlDbType.NVarChar, 20);
            p.Value = userName;
            return DataConfig.Exists(BusinessType.DBRead, sql, p);
        }

        /// <summary>
        /// 获取用户信息实体
        /// </summary>
        public UserEntity GetEntityById(long userId)
        {
            string sql = "select * from AdmUser with(nolock) where userId = @userId and status=1";
            SqlParameter p = new SqlParameter("@userId", SqlDbType.BigInt, 16);
            p.Value = userId;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            UserEntity model = this.GetEntityHelper(dt);
            return model;
        }

        /// <summary>
        /// 获取用户信息实体
        /// </summary>
        public UserEntity GetEntityByName(string userName)
        {
            string sql = "select * from AdmUser with(nolock) where userName = @userName and status=1";
            SqlParameter p = new SqlParameter("@userName", SqlDbType.NVarChar, 20);
            p.Value = userName;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            UserEntity model = this.GetEntityHelper(dt);
            return model;
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        public bool Delete(long userID, UserEntity optUser)
        {
            string sql = "update AdmUser set status=0 where userId = @userId";
            SqlParameter p = new SqlParameter("@userId", SqlDbType.BigInt, 16);
            p.Value = userID;
            int affectRows = DataConfig.QueryNoRecords(BusinessType.DBWrite, sql, p);
            return affectRows > 0;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        public DataTable GetList(long departmentID)
        {
            string sql = "select * from AdmUser with(nolock) where departmentID = @departmentID and status=1";
            SqlParameter p = new SqlParameter("@departmentID", SqlDbType.BigInt, 16);
            p.Value = departmentID;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            return dt;
        }
        /// <summary>
        /// 获取邮件通知用户列表
        /// </summary>
        public DataTable GetMailList(long departmentID)
        {
            string sql = "select * from AdmUser with(nolock) where departmentID = @departmentID and (UserType=103 or UserType=105) and status=1";
            SqlParameter p = new SqlParameter("@departmentID", SqlDbType.BigInt, 16);
            p.Value = departmentID;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            return dt;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        public DataTable GetList(string strWhere, int pageSize, int page, ref int allCount)
        {
            DataTable dt = new DataTable();
            string condition = " where status=1" + strWhere;
            string sqlCount = "select count(1) from AdmUser with(nolock) " + condition;
            allCount = (int)DataConfig.GetSingle(BusinessType.DBRead, sqlCount);
            if (allCount > 0)
            {
                int startNum = (page - 1) * pageSize + 1;
                int endNum = page * pageSize;
                string sqlInner = "select *,departmentname = (select top 1 Name from Department b with(nolock) where a.DepartmentID=b.ID)"
                    + ",gangweiname = (select top 1 TypeName from UserPower b with(nolock) where a.UserType=b.UserType) "
                    + ",ROW_NUMBER() over(order by DepartmentID,sName) as RowNumber from AdmUser a with(nolock) " + condition;
                string sqlUserList = string.Format("SELECT * FROM ({0}) T WHERE T.RowNumber BETWEEN {1} AND {2} order by RowNumber asc", sqlInner, startNum, endNum);
                dt = DataConfig.QueryRecords(BusinessType.DBRead, sqlUserList);
            }
            return dt;
        }

        /// <summary>
        /// 增加用户
        /// </summary>
        /// <param name="model">用户信息实体</param>
        /// <param name="optUser">操作用户</param>
        /// <returns>成功用户编号,失败返回0</returns>
        public long Add(UserEntity model, UserEntity optUser)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.UserName != null)
            {
                strSql1.Append("UserName,");
                strSql2.Append("'" + model.UserName + "',");
            }
            if (model.Pwd != null)
            {
                strSql1.Append("Pwd,");
                strSql2.Append("'" + model.Pwd + "',");
            }
            if (model.SName != null)
            {
                strSql1.Append("SName,");
                strSql2.Append("'" + model.SName + "',");
            }
            if (model.UserType != null)
            {
                strSql1.Append("UserType,");
                strSql2.Append("" + model.UserType + ",");
            }
            if (model.DepartmentID != null)
            {
                strSql1.Append("DepartmentID,");
                strSql2.Append("" + model.DepartmentID + ",");
            }
            if (model.EMail != null)
            {
                strSql1.Append("EMail,");
                strSql2.Append("'" + model.EMail + "',");
            }
            if (model.LastLoginTime != null)
            {
                strSql1.Append("LastLoginTime,");
                strSql2.Append("'" + model.LastLoginTime + "',");
            }
            if (model.UserRights != null)
            {
                strSql1.Append("UserRights,");
                strSql2.Append("'" + model.UserRights + "',");
            }
            if (model.OptUserID != null)
            {
                strSql1.Append("OptUserID,");
                strSql2.Append("" + model.OptUserID + ",");
            }
            if (model.Status != null)
            {
                strSql1.Append("Status,");
                strSql2.Append("" + model.Status + ",");
            }
            if (model.AddTime != null)
            {
                strSql1.Append("AddTime,");
                strSql2.Append("'" + model.AddTime + "',");
            }
            strSql.Append("insert into AdmUser(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DataConfig.GetSingle(BusinessType.DBWrite, strSql.ToString());
            long userID = 0;
            if (obj != null && obj != DBNull.Value)
            {
                long.TryParse(obj.ToString(), out userID);
            }
            return userID;
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="model">修改用户</param>
        /// <param name="optUser">操作用户</param>
        /// <returns>操作结果</returns>
        public bool Update(UserEntity model, UserEntity optUser)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update AdmUser set ");
            if (model.UserName != null)
            {
                strSql.Append("UserName='" + model.UserName + "',");
            }
            if (model.Pwd != null && model.Pwd != "")
            {
                strSql.Append("Pwd='" + model.Pwd + "',");
            }
            if (model.SName != null)
            {
                strSql.Append("SName='" + model.SName + "',");
            }
            if (model.UserType != null)
            {
                strSql.Append("UserType=" + model.UserType + ",");
            }
            if (model.DepartmentID > -1)
            {
                strSql.Append("DepartmentID=" + model.DepartmentID + ",");
            }
            if (model.EMail != null)
            {
                strSql.Append("EMail='" + model.EMail + "',");
            }
            if (model.LastLoginTime != null)
            {
                strSql.Append("LastLoginTime='" + model.LastLoginTime + "',");
            }
            if (model.UserRights != null)
            {
                strSql.Append("UserRights='" + model.UserRights + "',");
            }
            if (model.OptUserID != null)
            {
                strSql.Append("OptUserID=" + model.OptUserID + ",");
            }
            if (model.AddTime != null)
            {
                strSql.Append("AddTime='" + model.AddTime + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where UserId=" + model.UserId + "");
            int rows = DataConfig.QueryNoRecords(BusinessType.DBWrite, strSql.ToString());
            return rows > 0;
        }

        /// <summary>
        /// 更新用户最后登录时间
        /// </summary>
        /// <param name="userID">用户编号</param>
        /// <returns>操作结果</returns>
        public bool UpdateLoginTime(long userID)
        {
            string sql = "update AdmUser set lastLoginTime=GETDATE() where userId=@userId";
            SqlParameter p = new SqlParameter("@userId", SqlDbType.BigInt, 16);
            p.Value = userID;
            int rows = DataConfig.QueryNoRecords(BusinessType.DBWrite, sql.ToString(), p);
            return rows > 0;
        }

        /// <summary>
        /// 从DataTable中得到一个实体对象
        /// </summary>
        private UserEntity GetEntityHelper(DataTable dt)
        {
            UserEntity model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                model = new UserEntity();

                if (dt.Rows[0]["UserId"] != null && dt.Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = long.Parse(dt.Rows[0]["UserId"].ToString());
                }
                if (dt.Rows[0]["UserName"] != null && dt.Rows[0]["UserName"].ToString() != "")
                {
                    model.UserName = dt.Rows[0]["UserName"].ToString();
                }
                if (dt.Rows[0]["Pwd"] != null && dt.Rows[0]["Pwd"].ToString() != "")
                {
                    model.Pwd = dt.Rows[0]["Pwd"].ToString();
                }
                if (dt.Rows[0]["SName"] != null && dt.Rows[0]["SName"].ToString() != "")
                {
                    model.SName = dt.Rows[0]["SName"].ToString();
                }
                if (dt.Rows[0]["UserType"] != null && dt.Rows[0]["UserType"].ToString() != "")
                {
                    model.UserType = int.Parse(dt.Rows[0]["UserType"].ToString());
                }
                if (dt.Rows[0]["DepartmentID"] != null && dt.Rows[0]["DepartmentID"].ToString() != "")
                {
                    model.DepartmentID = int.Parse(dt.Rows[0]["DepartmentID"].ToString());
                }
                if (dt.Rows[0]["EMail"] != null && dt.Rows[0]["EMail"].ToString() != "")
                {
                    model.EMail = dt.Rows[0]["EMail"].ToString();
                }
                if (dt.Rows[0]["LastLoginTime"] != null && dt.Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = DateTime.Parse(dt.Rows[0]["LastLoginTime"].ToString());
                }
                if (dt.Rows[0]["UserRights"] != null && dt.Rows[0]["UserRights"].ToString() != "")
                {
                    model.UserRights = dt.Rows[0]["UserRights"].ToString();
                }
                if (dt.Rows[0]["OptUserID"] != null && dt.Rows[0]["OptUserID"].ToString() != "")
                {
                    model.OptUserID = long.Parse(dt.Rows[0]["OptUserID"].ToString());
                }
                if (dt.Rows[0]["Status"] != null && dt.Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(dt.Rows[0]["Status"].ToString());
                }
                if (dt.Rows[0]["AddTime"] != null && dt.Rows[0]["AddTime"].ToString() != "")
                {
                    model.AddTime = DateTime.Parse(dt.Rows[0]["AddTime"].ToString());
                }
            }
            return model;
        }
    }
}
